' ##############################################################################
' Components
' ##############################################################################
with frmStates
	.width = 400: .Height = 480
	.caption = "States"
	.delbordericons 2
	.borderstyle = 1
	.center
	.onclose = frmStates_OnClose
	.onshow = frmStates_OnShow
	SetWindowLong .HANDLE, -8, 0					' minimalizing correction
	SetWindowLong application.HANDLE, -8, .HANDLE 	' minimalizing correction
	.Font.Name = "Tahoma"
	.Font.Color = rgb(&H00, &H00, &H66)
	.Font.CharSet = 238 'EASTEUROPE_CHARSET (you can change it - see rapidq.inc)
end with

	with lviStates
		.parent = frmStates
		.width = 280: .align = 3
		.viewstyle = 3 'vsReport
		.gridlines = 1
		.addcolumns "state_id":		.Column(0).width = 70
		.addcolumns "state_name":	.Column(1).width = 100
		.addcolumns "state_code":	.Column(2).width = 88
		.rowSelect = 1
		.onchange = lviStates_onChange
		.ondblclick = btnStatesUpdate_OnClick
		.onKeyPress = lviStates_onKeyPress
		.readonly = 1
	end with

	with pnlStates
		.parent = frmStates
		.Align = 5
		.bevelouter = 0
	end with

	with btnStatesAdd
		.parent =pnlStates
		.height = 30
		.align = 1
		.caption = "&Insert"
		.hint = "ShortCut: [SPACE]"
		.showhint = 1
		.flat = 1
		.onClick = btnStatesAdd_OnClick
	end with

	with btnStatesUpdate
		.parent =pnlStates
		.height = 30
		.align = 1
		.caption = "&Update"
		.hint = "ShortCut: [ENTER]"
		.showhint = 1
		.flat = 1
		.onClick = btnStatesUpdate_OnClick
	end with

	with btnStatesDelete
		.parent =pnlStates
		.height = 30
		.align = 1
		.caption = "&Delete"
		.hint = "ShortCut: [BACK SPACE]"
		.showhint = 1
		.flat = 1
		.onClick = btnStatesDelete_OnClick
	end with

	with gbxStatesOrder
		.parent =pnlStates
		.height = 40
		.align = 1
		.caption = " Order by "
	end with

	with cbxStatesOrder
		.parent = gbxStatesOrder
		.align = 5 ' client
		.style = 2 ' DropDownList
		.additems "state_id", "state_name", "state_code"
		.itemindex = 0 ' first item (-1 = no item)
		.onchange = cbxStatesOrder_OnChange
	end with

	with stbStates
		.parent = frmStates
		.addpanels "", " Author: Slavo (iss@post.sk)", " Version: 0.2"
		.panel(0).width = 145
		.panel(1).width = 160
		.sizegrip = 0
	end with

' ##############################################################################
' Subs
' ##############################################################################
sub frmStates_OnShow
	dim result1 	as long
	dim result2		as long
	dim sql			as string
	dim bChar		as byte
	dim iCounter	as word
	dim version		as string
	dim StringList as QStringList

	filename = "data.db"
	If fileexists (filename) = 0 then
		showMessage "Program is running the first time (maybe)." + chr$(13) + _
			"Database does not exists! I create new..."
		result1 = sqlite3_open (filename, db)
		if result1 = sqlite3_ok then
			sql = "create table tb_states(" + _
			"state_id INTEGER PRIMARY KEY," + _
			"state_name TEXT UNIQUE, " + _
			"state_code TEXT" + _
			");"
			result2 = str$(sqlite3_exec (db, sql, 0, 0, 0))
			if result2 = sqlite3_ok then
				showmessage "New database created successfully..."
			else: showMessage SQL3_errCode(result2)
			end if
		else: showMessage SQL3_errCode(result1)
		end if
	else:
		result1 = sqlite3_open (filename, db)
		if result1 = sqlite3_ok then
			StringList.Text = SQL3_gettable ("select * from tb_states", separ)
			FillItemsFromTable (StringList)

		Else: showMessage SQL3_errCode(result1)
		End if
	end if
	sqlite3_close (db)
	stbStates.Panel(0).Caption = " SQLite3.dll version: " + SQL3_libversion
end sub

' --------------------------------------------------------------------------
Sub frmStates_OnClose
	Dim ValExit as Integer
	ValExit = MessageDlg ("Exit program ???", 3, 1 or 2, 0)
	If ValExit = 2 or ValExit = 7 then Action = 0
end Sub

' --------------------------------------------------------------------------
SUB btnStatesAdd_OnClick
	dim sql as String
	dim result1 as long, result2 as long
	dim J as Long
	dim StringList as QStringList

	frmState1.Caption = "Add new state"
	ediStateName.Text = ""
	ediStateCode.Text = ""
	btnStatOK.Enabled = 0
	SetFocus (ediStateName.Handle)

	If frmState1.ShowModal = 1 then
		sql = "insert into tb_states (state_name, state_code) values ('" + ediStateName.EditText + "', '" + ediStateCode.EditText + "');"

		result1 = sqlite3_open (filename, db)
		if result1 = sqlite3_ok then
			result2 = sqlite3_exec (db, sql, 0, 0, 0)
			if result2 <> sqlite3_ok then
				showMessage SQL3_errCode(result2)
				SetFocus (lviStates.Handle)
				exit Sub
			end if
		else: showMessage SQL3_errCode(result1)
			SetFocus (lviStates.Handle)
			exit Sub
		end if
		sqlite3_close (db)

		StringList.Text = SQL3_gettable ("select * from tb_states order by " + _
			cbxStatesOrder.Item(cbxStatesOrder.ItemIndex), separ)
		FillItemsFromTable (StringList)
	end if
	SetFocus (lviStates.Handle)
end sub

' ------------------------------------------------------------------------------
sub btnStatesUpdate_OnClick
	if lviStates.ItemIndex = -1 then
		showMessage "Select some record first..."
		exit sub
	end if

	dim sql as string, StringList as QStringList
	dim result1 as long, result2 as long, J as Long

	frmState1.Caption = "Update: " + frmStates.Caption
	ediStateName.Text = lviStates.SubItem(lviStates.ItemIndex, 0)
	ediStateCode.Text = lviStates.SubItem(lviStates.ItemIndex, 1)
	btnStatOK.Enabled = 1
	SetFocus (ediStateName.Handle)
	If frmState1.ShowModal = 1 then
		result1 = sqlite3_open (filename, db)
		if result1 = sqlite3_ok then
			sql = "UPDATE tb_states SET state_name = '" + ediStateName.EditText _
			+ "', state_code = '" + ediStateCode.EditText + "' " _
			+ "WHERE state_id = '" + lviStates.Item(lviStates.itemIndex).Caption + "';"
			result2 = sqlite3_exec (db, sql, 0, 0, 0)
			if result2 <> sqlite3_ok then
				showMessage SQL3_errCode (result2)
				SetFocus (lviStates.Handle)
				exit Sub
			end if
		else: showMessage SQL3_errCode(result1)
			SetFocus (lviStates.Handle)
			exit Sub
		end if
		sqlite3_close (db)

		StringList.Text = SQL3_gettable ("select * from tb_states order by " + _
			cbxStatesOrder.Item(cbxStatesOrder.ItemIndex), separ)
		FillItemsFromTable (StringList)
	end if
	SetFocus (lviStates.Handle)
end sub

' ------------------------------------------------------------------------------
sub btnStatesDelete_OnClick
	if lviStates.ItemIndex = -1 then
		showMessage "Select some record first..."
		exit sub
	end if

	dim sql as string, StringList as QStringList
	dim result1 as long, result2 as long, J as Long

	result1 = messagedlg ("Delete record: " + lviStates.Item(lviStates.itemIndex).Caption _
		+ ". " + lviStates.SubItem(lviStates.ItemIndex, 0) + "?", 3, 1 or 2, 0)
	If result1 = 7 or result1 = 2 then exit sub

		result1 = sqlite3_open (filename, db)
		if result1 = sqlite3_ok then
			sql = "DELETE FROM tb_states WHERE state_id = '" + lviStates.Item(lviStates.itemIndex).Caption + "';"
			result2 = sqlite3_exec (db, sql, 0, 0, 0)
			if result2 <> sqlite3_ok then
				showMessage SQL3_errCode (result2)
				SetFocus (lviStates.Handle)
				exit Sub
			end if
		else: showMessage SQL3_errCode(result1)
			SetFocus (lviStates.Handle)
			exit Sub
		end if
		sqlite3_close (db)

		StringList.Text = SQL3_gettable ("select * from tb_states order by " + _
			cbxStatesOrder.Item(cbxStatesOrder.ItemIndex), separ)
		FillItemsFromTable (StringList)
end sub

' ------------------------------------------------------------------------------
sub btnStatesExit_OnClick
	frmStates.Close
end sub

' ------------------------------------------------------------------------------
sub lviStates_onChange
	if lviStates.ItemIndex = -1 then exit sub
	frmStates.Caption = lviStates.Item(lviStates.ItemIndex).Caption + ". "  + _
		lviStates.SubItem(lviStates.ItemIndex, 0) + " (" + _
		lviStates.SubItem(lviStates.ItemIndex, 1) + ")"
end sub

' ------------------------------------------------------------------------------
sub lviStates_onKeyPress
	Select case key
		case 8: btnStatesDelete_OnClick
		case 13: btnStatesUpdate_OnClick
		case 27: frmStates.Close
		case 32: btnStatesAdd_OnClick
	end select
end sub

' ------------------------------------------------------------------------------
sub FillItemsFromTable
	dim I as Long, RecordItem as String
	dim separ1 as word, separ2 as word ' for searching global separ (like "|")

	lviStates.clear
	' Fill items on listview
	For I = 1 to Sender.ItemCount - 1
		separ1 = Instr(Sender.Item(I), separ)
		lviStates.addItems Left$(Sender.Item(I), separ1 - 1)
		separ2 = separ1
		separ1 = Instr(separ2 + 1, Sender.Item(I), separ)
		while separ1 > 0
			RecordItem = Mid$(Sender.Item(I), separ2 + 1, separ1 - separ2 - 1)
			lviStates.addSubItem I-1, recordItem
			separ2 = separ1
			separ1 = Instr(separ2 + 1, Sender.Item(I), separ)
		wend
		recordItem = right$(sender.Item(I), len(sender.Item(I)) - separ2)
		lviStates.addSubItem (I-1, RecordItem)
	Next I
end sub

' ------------------------------------------------------------------------------
sub cbxStatesOrder_OnChange
	if lviStates.ItemCount < 2 then
		showMessage "Nothing to order..."
		exit sub
	end if

	dim StringList as QStringList

	StringList.Text = SQL3_gettable ("select * from tb_states order by " + _
		cbxStatesOrder.Item(cbxStatesOrder.ItemIndex), separ)
	FillItemsFromTable (StringList)
	SetFocus (lviStates.Handle)
end sub
